package com.cntcdt.api.db.dao;

import java.lang.reflect.Field;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.cntcdt.api.db.dao.base.ChongdianbaoDaoBase;
import com.cntcdt.api.db.model.ChongdianbaoModel;
import com.cntcdt.api.utils.LogUtils;
import com.cntcdt.api.utils.ReflectUtils;
import com.coola.jutil.sql.DBEngine;
import com.coola.jutil.sql.DBFactory;

/**
 * 这是cdt_chongdianbao的Dao实现类
 */
public class ChongdianbaoDao extends ChongdianbaoDaoBase   {
    /**
     * ChongdianbaoDao对象
     */
    private static ChongdianbaoDao instance = new ChongdianbaoDao();

    /**
     * 默认构造方法
     */
    private ChongdianbaoDao() {
    }

    /**
     * 获得对象实例
     */
    public static ChongdianbaoDao getInstance() { 
         return instance;
    } 
    
    /**
     * 插入一条数据
     * @param  model UserModel对象
     * @return 返回插入影响行数
     */
    public int insert(Object model) {
        DBEngine localEngine = DBFactory.getKeepConnDBEngine("main");
        StringBuffer sql = new StringBuffer("INSERT INTO " + TABLE + " (");
        StringBuffer stmts =  new StringBuffer(" values(");
        List<Object> sqlArgs = new ArrayList<Object>();
		Field[] superfields = model.getClass().getSuperclass().getDeclaredFields();
//		Field[] fields = userModel.getClass().getDeclaredFields();
		for(Field field : superfields){
			if(field.getName().equals("serialVersionUID") || field.getName().equals("id")){
				continue;
			}
			if(ReflectUtils.getter(model, field.getName())!=null){
				sql.append(" "+field.getName()+",");
				stmts.append("?,");				
				sqlArgs.add(ReflectUtils.getter(model, field.getName()));
			}
		}
		
		String sqlstr = sql.toString();
		sqlstr = sqlstr.substring(0, sqlstr.length()-1);
		sqlstr = sqlstr + ")";
		
		String stmtstr = stmts.toString();
		stmtstr = stmtstr.substring(0, stmtstr.length()-1);
		stmtstr = stmtstr + ")";
        
		LogUtils.log.info(sqlstr + stmtstr);
		
		Object[]  paramObj = sqlArgs.toArray();
        int id=0;
        try {
        	String tSql = sqlstr+stmtstr;
             id = localEngine.executeUpdate(tSql, paramObj);
             ResultSet rs = localEngine.executeQuery("select last_insert_id() as id");
             if (rs.next()) {
               id = rs.getInt("id");
             }
        }catch(Exception e) {
           e.printStackTrace();
        } finally {
           localEngine.close();
        }
        return id;
    }
    

    /**
     * 更新操作，自动根据userModel中已经设置的字段进行更新
     * 必须要设置userModel.id属性 和 其他任意一个属性
     * 
     * 返回更新结果
     */
    public boolean updateAny(Object model){
    	StringBuffer sql = new StringBuffer("update  "+TABLE+" set ");
		List<Object> sqlArgs = new ArrayList<Object>();
		Field[] superfields = model.getClass().getSuperclass().getDeclaredFields();
//		Field[] fields = userModel.getClass().getDeclaredFields();
		for(Field field : superfields){
			if(field.getName().equals("serialVersionUID") || field.getName().equals("id")){
				continue;
			}
			if(ReflectUtils.getter(model, field.getName())!=null){
				sql.append(" "+field.getName()+"=? ,");
				sqlArgs.add(ReflectUtils.getter(model, field.getName()));
			} 
		}
		
		String sqlstr = sql.toString();
		sqlstr = sqlstr.substring(0, sqlstr.length()-1);
		sqlstr = sqlstr + " where id=?";
		sqlArgs.add(ReflectUtils.getter(model,"id"));
		LogUtils.log.info(sqlstr);
		LogUtils.log.info(sqlArgs.toString());
		
		Object[]  paramObj = sqlArgs.toArray();
        try {
             return writeDBEngine.executeUpdate(sqlstr, paramObj)>0;
        } catch(Exception e) {
           e.printStackTrace();
        }
        return false;
    }
    
    
    /**
     * 按imei查询model
     * @param id id
     * @return model
     */
    public ChongdianbaoModel findByIMEI(String imei) {
        String sql = "SELECT * FROM " + TABLE + " WHERE imei = ?";
       Object[] paramObjs = {imei};
       List<ChongdianbaoModel > modelList = this.queryModelList(sql, paramObjs);
       if(modelList != null && modelList.size() > 0){ 
             return  modelList.get(0);
       } 
       return null;
   }
    

    /**微信公众号账号绑定旧账号,更新所有记录的userid
     * 获得model集合
     * @return 返回Model集合
     */
    public boolean bindWXUpdate(int userId , int userIdOld) {
        String sql = "update " + TABLE + " set  checkoutUserId=? where checkoutUserId=? " ;
        try {
			int re = writeDBEngine.executeUpdate(sql , new Object[]{userId,userIdOld});
			if(re > 0){
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
        return false;
    }
    
   
    /**
     * 按imei查询model
     * @param id id
     * @return model
     */
    public ChongdianbaoModel findByDeviceIdAndMAXCapacity(int DeviceId) {
        String sql = "SELECT * FROM " + TABLE + " WHERE DeviceId = ? and Status=1 and Capacity>35 and vstatus=0 and (CheckoutUserId=0 or CheckoutUserId is null) and isNonuse = 0 and cdbUseCnt < 1000 order by priority desc,Capacity desc limit 1";
       Object[] paramObjs = {DeviceId};
       List<ChongdianbaoModel > modelList = this.queryModelList(sql, paramObjs);
       if(modelList != null && modelList.size() > 0){ 
             return  modelList.get(0);
       } 
       return null;
   }
    
    
    /**
     * 查询出该设备所有的充电宝
     * @param id id
     * @return model
     */
    public List<ChongdianbaoModel> findByDeviceId(int DeviceId) {
        String sql = "SELECT * FROM " + TABLE + " WHERE DeviceId = ?";
       Object[] paramObjs = {DeviceId};
       List<ChongdianbaoModel > modelList = this.queryModelList(sql, paramObjs);
       return modelList;
   }
    
   public boolean deleteByDeviceIdAndDevicePosition(int DeviceId , int devicePosition ){
//	   String sql = "DELETE FROM " + TABLE + " WHERE DeviceId = ? and devicePosition=?";
	   String sql = "update "+TABLE + " set deviceId=0,devicePosition=0 where DeviceId = ? and devicePosition=? ";
       Object[] paramObjs = {DeviceId , devicePosition};
       try{
           return writeDBEngine.executeUpdate(sql, paramObjs)>0; 
       } catch (Exception e) {
            e.printStackTrace();
       }
       return false;
	   
	   
   }
   
   public Map<String,Integer> searchCnt(){
	   Map<String,Integer> resultMap = new HashMap<String,Integer>();
	   String sql1 = "select count(id) as cnt from "+TABLE +" where imei not in(select cdb from cdt_check_history where type = 9) and checkoutUserId <> 0";
	   try {
		Object[] paramObjs = null;
		ResultSet rs = readDBEngine.executeQuery(sql1, paramObjs);
		while(rs.next()){
			resultMap.put("userCDB", rs.getInt("cnt"));
		}
	   } catch (SQLException e) {
		e.printStackTrace();
	   }
	   
	   String sql2 = "select count(id) as cnt from "+TABLE + " where deviceId <> 0";
	   try {
		Object[] paramObjs = null;
		ResultSet rs = readDBEngine.executeQuery(sql2, paramObjs);
		while(rs.next()){
			resultMap.put("deviceCDB", rs.getInt("cnt"));
		}
	   } catch (SQLException e) {
		e.printStackTrace();
	   }
	   
	   String sql3 = "select count(id) as cnt from "+TABLE + " where imei in(select cdb from cdt_check_history where type = 9) and checkoutUserId <> 0";
	   try {
		Object[] paramObjs = null;
		ResultSet rs = readDBEngine.executeQuery(sql3, paramObjs);
		while(rs.next()){
			resultMap.put("buyCDB", rs.getInt("cnt"));
		}
	   } catch (SQLException e) {
		e.printStackTrace();
	   }
	   
//	  String sql4 = "select count(id) as cnt from "+TABLE + " where deviceId = 0 and checkoutUserId = 0 and IsDebarLostCnt = 0 and lastTerminal in(select terminal from statistic.cdt_group_device where groupId in(select id from statistic.cdt_group_user where opStatus = 0 and id <> 82) group by terminal)";
	  String sql4 = "select count(id) as cnt from "+TABLE + " where deviceId = 0 and checkoutUserId = 0 and IsDebarLostCnt = 0 and lastTerminal in(select terminal from statistic.cdt_group_device where groupId in(select id from statistic.cdt_group_user where opStatus = 0 and id <> 82) group by terminal) and imei NOT in(select a.imei from  (select * from cdt.cdt_chongdianbao where checkoutUserId=0 and deviceId=0) as a INNER JOIN (select * from cdt.cdt_device_exception where repairTime is NULL ) as b on a.lastTerminal=b.terminal and a.lastPosition=b.position)";
	   try {
		Object[] paramObjs = null;   
		ResultSet rs = readDBEngine.executeQuery(sql4, paramObjs);
		while(rs.next()){
			resultMap.put("lostCDB", rs.getInt("cnt"));
		}
	   } catch (SQLException e) {
		e.printStackTrace();
	   }
	   return resultMap;
   }
    
   public List<ChongdianbaoModel> findByStorTime(int sortTime){
	  sortTime = sortTime*20;
      String sql = "SELECT * FROM " + TABLE + " order by isNonuse desc,updateTime limit ?,20";
      Object[] paramObjs = {sortTime};
      List<ChongdianbaoModel > modelList = this.queryModelList(sql, paramObjs);
      return modelList;
   }
   /**
    * 排除坏仓中的充电宝
    * @param sortTime
    * @return
    */
   public List<ChongdianbaoModel> findByRemoveBrokenPositionCDB(int sortTime){
	   sortTime = sortTime*20;
       //String sql = "select * from "+TABLE+" WHERE imei NOT in(select a.imei from  (select * from cdt.cdt_chongdianbao where checkoutUserId=0 and deviceId=0) as a INNER JOIN (select * from cdt.cdt_device_exception where repairTime is NULL ) as b on a.lastTerminal=b.terminal and a.lastPosition=b.position) order by isNonuse desc,updateTime limit ?,20";
	   String sql = "select * from "+TABLE+ " where deviceId = 0 and checkoutUserId = 0 and lastTerminal in(select terminal from statistic.cdt_group_device where groupId in(select id from statistic.cdt_group_user where opStatus = 0 and id <> 82) group by terminal) and imei not in(select a.imei from  (select * from cdt.cdt_chongdianbao where checkoutUserId=0 and deviceId=0) as a INNER JOIN (select * from cdt.cdt_device_exception where repairTime is NULL ) as b on a.lastTerminal=b.terminal and a.lastPosition=b.position) order by isDebarLostCnt,updateTime limit ?,20";
	   Object[] paramObjs = {sortTime};
	   List<ChongdianbaoModel > modelList = this.queryModelList(sql, paramObjs);
	   return modelList;
   }
   
   public List<ChongdianbaoModel> findBySearchContent(String searchContent,int sortTime){
	   sortTime = sortTime*20;
      String sql = "select * from "+TABLE+" where deviceId in (select id from cdt_device where terminal like ? or shopName like ?) or imei like ? limit ?,50";
      Object[] paramObjs = {searchContent,searchContent,searchContent,sortTime};
      List<ChongdianbaoModel > modelList = this.queryModelList(sql, paramObjs);
      return modelList;
   }
   
   public List<ChongdianbaoModel> findByLostCDB(int sortTime){
	   	  sortTime = sortTime*20;
	   	  //不排除异常仓道充电宝
	      String sql = "select * from "+TABLE+ " where deviceId = 0 and checkoutUserId = 0 and lastTerminal in(select terminal from statistic.cdt_group_device where groupId in(select id from statistic.cdt_group_user where opStatus = 0 and id <> 82) group by terminal) order by isDebarLostCnt,updateTime limit ?,20";
	      Object[] paramObjs = {sortTime};
	      List<ChongdianbaoModel > modelList = this.queryModelList(sql, paramObjs);
	      return modelList;
   }
   
   public ChongdianbaoModel findByCDB(String cdb) {
	      String sql = "select * from "+TABLE+" where imei = ?";
	      Object[] paramObjs = {cdb};
	      List<ChongdianbaoModel > modelList = this.queryModelList(sql, paramObjs);
	      if(modelList.size() > 0){
	    	  return modelList.get(0);
	      }
	      return null;
   }
   
   public List<ChongdianbaoModel> findByLostCDBSearchContent(String searchContent,int sortTime){
	   	  sortTime = sortTime*20;
	      String sql = "select * from "+TABLE+" where (imei like ? or lastTerminal like ?) and deviceId = 0 and checkoutUserId = 0 and lastTerminal in(select terminal from statistic.cdt_group_device where groupId in(select id from statistic.cdt_group_user where opStatus = 0) group by terminal) limit ?,20";
	      Object[] paramObjs = {searchContent,searchContent,sortTime};
	      List<ChongdianbaoModel > modelList = this.queryModelList(sql, paramObjs);
	      return modelList;
	   }
   
   public int findByPriorityIs0(int deviceId){
	   String sql2 = "select count(DISTINCT(devicePosition)) as cnt from "+TABLE + " where deviceId = ? and priority = 0";
	   try {
		Object[] paramObjs = {deviceId};
		ResultSet rs = readDBEngine.executeQuery(sql2, paramObjs);
		while(rs.next()){
			return rs.getInt("cnt");
		}
	   } catch (SQLException e) {
		e.printStackTrace();
	   }
	   return 0;
   }
   
   /**
    * 统计坏仓中充电宝数
    * @return
    */
   public Map<String,Integer> brokenPositionCDBCnt(){
	   
	   Map<String,Integer> resultMap = new HashMap<String,Integer>();
	   
	   String sql1 = "select count(imei) as cnt from (select * from "+TABLE+" where checkoutUserId=0 and deviceId=0) as a INNER JOIN (select * from cdt.cdt_device_exception where repairTime is NULL ) as b on a.lastTerminal=b.terminal and a.lastPosition=b.position";
	   try {
		Object[] paramObjs = null;
		ResultSet rs = readDBEngine.executeQuery(sql1, paramObjs);
		while(rs.next()){
			resultMap.put("exceptionPositionCDB", rs.getInt("cnt"));
		}
	   } catch (SQLException e) {
		e.printStackTrace();
	   }
	   
	   String sql4 = "select count(id) as cnt from "+TABLE + " where deviceId = 0 and checkoutUserId = 0 and IsDebarLostCnt = 0 and lastTerminal in(select terminal from statistic.cdt_group_device where groupId in(select id from statistic.cdt_group_user where opStatus = 0 and id <> 82) group by terminal) and imei NOT in(select a.imei from  (select * from cdt.cdt_chongdianbao where checkoutUserId=0 and deviceId=0) as a INNER JOIN (select * from cdt.cdt_device_exception where repairTime is NULL ) as b on a.lastTerminal=b.terminal and a.lastPosition=b.position)";
	   try {
		Object[] paramObjs = null;   
		ResultSet rs = readDBEngine.executeQuery(sql4, paramObjs);
		while(rs.next()){
			resultMap.put("lostCDB", rs.getInt("cnt"));
		}
	   } catch (SQLException e) {
		e.printStackTrace();
	   }
	   
	   return resultMap;
   }
    
}
